﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.OleDb;
namespace Lab9
{
    public partial class ServerAjax : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String user_input = Request.QueryString["q"];
            String return_value = "";

            try // try to open the database...
            {
                //http://cis-iis2.temple.edu/Spring2012/4344_tuc32262/ServerAjax.aspx?q=v
                

                // put database information (type, location) into a string
                string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/App_Data/myDB.accdb");
                //Server.MapPath("\\App_Data\\myDB.accdb");
                // string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                //Server.MapPath("videoDb.accdb"); 

                

                // use this string (with DB info) to initialize a db connection.
                OleDbConnection con = new OleDbConnection(strCon);

                // open the database connection (would be on the 
                // database server if sql server or oracle).
                con.Open();

                // Put this next code in a try/catch since it's easy to run 
                // into errors, e.g., cant find database or table or field
                // names misspelled, or other sql syntax error.

                try
                { // to read from the database

                    // put the desired sql statement into a string
                    string sql = "SELECT * FROM editors_t where FullName like @nameLike order by FullName ASC";

                    // initialize a command object with the db connection
                    // info and the desired sql select statement.
                    OleDbCommand cmd = new OleDbCommand(sql, con);

                    cmd.Parameters.AddWithValue("@namelike", user_input + "%"); // replace parameter in above sql string. % is wild card character.

                    // now start executing the sql select statement
                    OleDbDataReader reader = cmd.ExecuteReader();

                    // this loop will execute one time for each row 
                    // that's returned by the sql select statement
                    if (reader.Read())
                    {
                        // reader.GetValue(0) is the first column specified in the 
                        // select statement.  (It doesnt matter what order the 
                        // fields are in design mode in MSAccess -- it only matters
                        // the order the fields were specified in your select 
                        // statement.  reader.GetValue(1) is the second column 
                        // specified in your select statement.
                        return_value += reader.GetValue(1).ToString() + "%" + reader.GetValue(2).ToString() + "%" + reader.GetValue(3).ToString();
                    }
                    else
                    {
                        return_value += "Customer not found<br/>";
                    }

                    // close the reader (this is not as important) -- 
                    // I think it gets closed automatically when the 
                    // connection gets closed.
                    reader.Close();

                    // close the connection
                    // this is VERY important.  If you dont close your connection, 
                    // you wont be able to run your program next time. 
                    // (File will be "in use").
                    con.Close();
                } // try to get data
                catch (Exception ex)
                {
                    return_value += "Exception while running SQL:" + ex.Message + "<br/>";
                    con.Close();
                }
            } // try to open db connection
            catch (Exception ex)
            {
                return_value += "Exception while trying to open the database<br/>" + ex.Message + "<br/>";
            }

            String formattedJson=(String)Convert.ChangeType(FormatJson(return_value), typeof(String));

            if (formattedJson == "-1")
            {
                Response.Write("Error: No editors match specified string");
                Response.End();

            }
            else
            {
                Response.Write(formattedJson);
                Response.End();
            }
        }
        public string FullyQualifiedApplicationPath
        {
            get
            {
                //Return variable declaration
                string appPath = null;

                //Getting the current context of HTTP request
                var context = HttpContext.Current;

                //Checking the current context content
                if (context != null)
                {
                    //Formatting the fully qualified website url/name
                    appPath = string.Format("{0}://{1}{2}{3}",
                                            context.Request.Url.Scheme,
                                            context.Request.Url.Host,
                                            context.Request.Url.Port == 80
                                                ? string.Empty
                                                : ":" + context.Request.Url.Port,
                                            context.Request.ApplicationPath);
                }

                if (!appPath.EndsWith("/"))
                    appPath += "/";

                return appPath;
            }
        }

        public object FormatJson(String str)
        {
            string json="";
            string[] splitStr;
            string desc="";
            string name="";
            string img="";

            try
            {
                splitStr = str.Split('%');
                name = splitStr[0];
                desc = splitStr[1];
                img = FullyQualifiedApplicationPath + splitStr[2];

                json = "{FullName:" + "'" + name + "', Description:" + "'" + desc + "', image:" + "'" + img + "'}";
            }
            catch (Exception ex)
            {
                return -1;
            }
            return json;
        }

   

    }
}